USE Fresher14_G1
GO

------------------------------ Begin Procedure sp_ViewContractor -------------------------
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_ViewContractor' AND TYPE = 'P')
DROP PROC sp_ViewContractor
GO

CREATE PROCEDURE sp_ViewContractor
AS
BEGIN
	SELECT ContractorId, Username, Password, FullName, PhoneNumber1, PhoneNumber2, Gender, IsActive, Branch, SortCode 
		FROM [Account] INNER JOIN [Contractor] ON [Account].AccountId = [Contractor].AccountId
END
------------------------------ End Procedure sp_ViewContractor -------------------------
GO

------------------------------ Begin Procedure sp_GetContractorByContractorId -------------------------
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_GetContractorByContractorId' AND TYPE = 'P')
DROP PROC sp_GetContractorByContractorId
GO

CREATE PROCEDURE sp_GetContractorByContractorId
	@ContractorId NVARCHAR(36)
AS
BEGIN
	SELECT ContractorId, Username, Password, FullName, PhoneNumber1, PhoneNumber2, Gender, IsActive, Branch, SortCode 
		FROM [Account] INNER JOIN [Contractor] ON [Account].AccountId = [Contractor].AccountId WHERE ContractorId = @ContractorId
END
GO
EXEC sp_GetContractorByContractorId '61f00f70-fb9a-430a-b58b-313261f360a7'

------------------------------ End Procedure sp_GetContractorByContractorId -------------------------
GO
------------------------------ Begin Procedure sp_CreateContractor -------------------------
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_CreateContractor' AND TYPE = 'P')
DROP PROC sp_CreateContractor
GO

CREATE PROCEDURE sp_CreateContractor
	@Username VARCHAR(50),
	@Password VARCHAR(50),
	@FullName NVARCHAR(50),
	@PhoneNumber1 VARCHAR(20),
	@PhoneNumber2 VARCHAR(20),
	@Gender NVARCHAR(20),
	@IsActive BIT,
	@Branch CHAR(10),
	@SortCode CHAR(10)
AS
	DECLARE @RoleId UNIQUEIDENTIFIER
	SET @RoleId = (SELECT RoleId FROM [Role] WHERE RoleName = 'Contractor')
	DECLARE @AccountId UNIQUEIDENTIFIER
	SET @AccountId = NEWID()

	BEGIN TRANSACTION
		INSERT INTO [Account](AccountId, RoleId, Username, Password, FullName, PhoneNumber1, PhoneNumber2, Gender, IsActive)
			VALUES (@AccountId, @RoleId, @Username, @Password, @FullName, @PhoneNumber1, @PhoneNumber2, @Gender, @IsActive)
		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END

		INSERT INTO [Contractor](AccountId, Branch, SortCode)
			VALUES (@AccountId, @Branch, @SortCode)
		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END

		INSERT INTO [SystemEventLog](SystemEventType, Description)
			VALUES ('Information', 'Create new contractor')
	COMMIT
GO

EXEC sp_CreateContractor 'contractor', '123123', 'contractor', '123456789', '987987987', 'other', 'true', '123', '312'
------------------------------ End Procedure sp_CreateContractor -------------------------
GO

------------------------------ Begin Procedure sp_UpdateContractor -------------------------
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_UpdateContractor' AND TYPE = 'P')
DROP PROC sp_UpdateContractor
GO

CREATE PROCEDURE sp_UpdateContractor
	@ContractorId UNIQUEIDENTIFIER,
	@PhoneNumber1 VARCHAR(20),
	@PhoneNumber2 VARCHAR(20),
	@Gender NVARCHAR(20),
	@Branch CHAR(10),
	@SortCode CHAR(10)
AS
	BEGIN TRANSACTION
		UPDATE [Account] SET PhoneNumber1 = @PhoneNumber1, PhoneNumber2=@PhoneNumber2, Gender=@Gender
							WHERE AccountId = (SELECT AccountId FROM Contractor 
													WHERE ContractorId = @ContractorId)
		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END

		UPDATE [Contractor] SET Branch = @Branch, SortCode = @SortCode
			WHERE ContractorId = CAST(@ContractorId AS UNIQUEIDENTIFIER)
		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END

		INSERT INTO [SystemEventLog](SystemEventType, Description)
			VALUES ('Information', 'Update contractor')
	COMMIT
GO
exec sp_UpdateContractor '61F00F70-FB9A-430A-B58B-313261F360A7', 
'345', '64', 'Female', 'Toshiba', '147'

exec sp_UpdateContractor 'da65e0fa-d24f-4c90-be92-5c956c671435', 
'123456789', '987987987', 'Male', '123', '312'
------------------------------ End Procedure sp_UpdateContractor -------------------------

GO

------------------------------ Begin Procedure sp_CreateConsultant -------------------------
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_CreateConsultant' AND TYPE = 'P')
DROP PROC sp_CreateConsultant
GO

CREATE PROCEDURE sp_CreateConsultant
	@Username VARCHAR(50),
	@Password VARCHAR(50),
	@FullName NVARCHAR(50),
	@PhoneNumber1 VARCHAR(20),
	@PhoneNumber2 VARCHAR(20),
	@Gender NVARCHAR(20),
	@IsActive BIT,
	@Address NVARCHAR(100),
	@RegisterNumber INT,
	@VatNumber INT
AS
	DECLARE @RoleId UNIQUEIDENTIFIER
	SET @RoleId = (SELECT RoleId FROM [Role] WHERE RoleName = 'Consultant')
	DECLARE @AccountId UNIQUEIDENTIFIER
	SET @AccountId = NEWID()

	BEGIN TRANSACTION
		INSERT INTO [Account](AccountId, RoleId, Username, Password, FullName, PhoneNumber1, PhoneNumber2, Gender, IsActive)
			VALUES (@AccountId, @RoleId, @Username, @Password, @FullName, @PhoneNumber1, @PhoneNumber2, @Gender, @IsActive)
		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END

		INSERT INTO [Consultant](AccountId, Address, RegisterNumber, VatNumber)
			VALUES (@AccountId, @Address, @RegisterNumber, @VatNumber)
		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END

		INSERT INTO [SystemEventLog](SystemEventType, Description)
			VALUES ('Information', 'Create new consultant')
	COMMIT
GO

EXEC sp_CreateConsultant 'consultant', '123123', 'consultant', '123456789', '987987987', 'other', 'true', 'Tung Cau', '1', '12'
------------------------------ End Procedure sp_CreateConsultant -------------------------
GO
------------------------------ Begin Procedure sp_CreateClient -------------------------
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_CreateClient' AND TYPE = 'P')
DROP PROC sp_CreateClient
GO

CREATE PROCEDURE sp_CreateClient
	@Username VARCHAR(50),
	@Password VARCHAR(50),
	@FullName NVARCHAR(50),
	@PhoneNumber1 VARCHAR(20),
	@PhoneNumber2 VARCHAR(20),
	@Gender NVARCHAR(20),
	@IsActive BIT,
	@CompanyName NVARCHAR(50),
	@CompanyRegisterNumber INT,
	@CompanyVatNumber INT,
	@CompanyPostCode INT,
	@CompanyAddress NVARCHAR(200)
AS
	DECLARE @RoleId UNIQUEIDENTIFIER
	SET @RoleId = (SELECT RoleId FROM [Role] WHERE RoleName = 'Client')
	DECLARE @AccountId UNIQUEIDENTIFIER
	SET @AccountId = NEWID()

	BEGIN TRANSACTION
		INSERT INTO [Account](AccountId, RoleId, Username, Password, FullName, PhoneNumber1, PhoneNumber2, Gender, IsActive)
			VALUES (@AccountId, @RoleId, @Username, @Password, @FullName, @PhoneNumber1, @PhoneNumber2, @Gender, @IsActive)
		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END

		INSERT INTO [Client](AccountId, CompanyName, CompanyRegisterNumber, CompanyVatNumber,CompanyPostCode,CompanyAddress)
			VALUES (@AccountId, @CompanyName, @CompanyRegisterNumber, @CompanyVatNumber, @CompanyPostCode, @CompanyAddress)
		
		IF @@ERROR <> 0
		BEGIN
			ROLLBACK
			RETURN
		END

		INSERT INTO [SystemEventLog](SystemEventType, Description)
			VALUES ('Information', 'Create new client')
	COMMIT
GO

EXEC sp_CreateClient 'client', '123123', 'client', '123456789', '987987987', 'other', 'true', 'FPT', '3212312', '12312321' , '21312313' ,'Ha Noi'
------------------------------ End Procedure sp_CreateConsultant -------------------------

GO

------------------------------ Begin Procedure sp_CreateClient -------------------------
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_GetUsernameNumber' AND TYPE = 'P')
DROP PROC sp_GetUsernameNumber
GO

CREATE PROCEDURE sp_GetUsernameNumber
(
	@Username NVARCHAR(20)
)
AS
BEGIN
	DECLARE @checkUserExist INT
	SET @checkUserExist = (SELECT COUNT(Username) FROM Account WHERE Username = @username)
	IF @checkUserExist = 0
		SELECT 0
	ELSE
		SELECT COUNT(Username) + 1 FROM Account WHERE Username LIKE @Username + '[0-9]%'
end
